# author:       李皓
# email:        133465202@qq.com
# repository:   https://gitee.com/ButchQiuQiu/
import param
import pymysql

import Param
from Param import param, runDict

"""
SQL服务模块
"""
connection = pymysql.connect(host=param["mysql"]["host"],
                             port=param["mysql"]["port"],
                             user=param["mysql"]["username"],
                             passwd=param["mysql"]["password"],
                             db=param["mysql"]["DB"],
                             charset=param["mysql"]["CHARSET"])


def getListDictByExecuteSql(sql: str) -> list:
    """
    执行SQL返回结果
    :param sql: sql
    :return: [{"column":value},{"column":value},...]
    """
    # 初始化连接

    cursor = connection.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    return data


def closeMysql():
    connection.close()


def getAllTabelName() -> list:
    """
    获取所有的表名
    :return:
    """
    sql: str = "select table_name from information_schema.tables where table_schema='{}'".format(param["mysql"]["DB"])
    tableNameListDict = getListDictByExecuteSql(sql)
    tableNameList: list = [i['table_name'] for i in tableNameListDict]
    return tableNameList


def getColumnListByTableName(tableName: str) -> list:
    """
    获取表的所有字段名
    :param tableName:   表名
    :return:            字段名列表
    """
    sql: str = "select COLUMN_NAME from information_schema.COLUMNS where table_name ='{}' and table_schema='{}'".format(
        tableName, param["mysql"]["DB"])
    tableNameListDict: list = getListDictByExecuteSql(sql)
    tableNameList: list = [tableNameDict["COLUMN_NAME"] for tableNameDict in tableNameListDict]
    return tableNameList


def getDDLByTableName(tableName: str) -> str:
    """
    获取表的DDL
    :return:
    """
    sql: str = "show create table " + tableName
    result: str = getListDictByExecuteSql(sql)[0]['Create Table']
    return result


def getColumnDDLByTableDDL(tableDDL: str, columnName: str) -> str:
    """
    获取字段所属DDL, 并且过滤掉注释部分
    :param tableDDL:    表的DDL
    :param columnName:  列的DDL
    :return:
    """
    start: int = tableDDL.find("`{}`".format(columnName))
    end = start + tableDDL[start:].find(",\n")
    if tableDDL[start:end].find("COMMENT") != -1:
        end = start + tableDDL[start:end].find("COMMENT")
    return tableDDL[start:end]


def saveAndExecuteDDLByTableColumnComment(tableName: str, columnName: str, comment: str):
    sourceDDL = runDict[tableName]["column"][columnName]["sourceDDL"]
    runDict[tableName]["column"][columnName]["finalSQL"] = "alter table {} modify column {} COMMENT '{}'".format(
        tableName, sourceDDL, comment)
    print(runDict[tableName]["column"][columnName]["finalSQL"])
    getListDictByExecuteSql(runDict[tableName]["column"][columnName]["finalSQL"])
